package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import base.DbConnect;
import base.Sql;
import model.Department;
import model.Manager;

public class ManagerDao {
	
	public static int add(Manager manager) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		String sql = "insert into teacher values (?,?,?,?,?,?)";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, manager.getT_id());
			stmt.setString(2, manager.getT_name());
			stmt.setString(3, manager.getT_password());
			stmt.setString(4, manager.getT_tel());
			stmt.setString(5, manager.getM_id());
			stmt.setString(6, manager.getIssuper());
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}
	
	
	public static model.Manager getManager(String t_id) {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		model.Manager manager = new model.Manager();
		try {
			stmt = conn.createStatement();
			String sql;
			//t_id
			sql = "SELECT * FROM teacher where t_id = '" + t_id + "'";
			rs = stmt.executeQuery(sql);
			if (rs.next()) {
				manager.setM_id(rs.getString("m_id"));
				manager.setT_id(rs.getString("t_id"));
				manager.setT_name(rs.getString("t_name"));
				manager.setT_password(rs.getString("t_password"));
				manager.setT_tel(rs.getString("t_tel"));
				manager.setIssuper(rs.getString("issuper"));
			}
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				stmt.close();
				conn.close();
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return manager;
	}

	public static int updata(Manager manager) {
		Connection conn = DbConnect.getConnect();		
		PreparedStatement stmt = null;
		int res = -1;
		try {
			String sql = "update teacher set t_id = ? ,t_name = ? ,t_password = ? ,t_tel = ? ,m_id = ? ,issuper = ? where t_id = ?";
			stmt = conn.prepareStatement(sql);
			stmt.setString(1, manager.getT_id());
			stmt.setString(2, manager.getT_name());
			stmt.setString(3, manager.getT_password());
			stmt.setString(4, manager.getT_tel());
			stmt.setString(5, manager.getM_id());
			stmt.setString(6, manager.getIssuper());
			stmt.setString(7, manager.getT_id());
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return res;
	}
	
	public static ArrayList<Manager> select() {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		ArrayList<Manager> arrayList = new ArrayList<Manager>();
		
		String sql;
		sql = "SELECT * FROM teacher";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				//private String t_id, t_name,t_tel,
//				t_password, m_id, issuper;
				Manager manager = new Manager();
				manager.setT_id(rs.getString("t_id"));
				manager.setT_name(rs.getString("t_name"));
				manager.setT_tel(rs.getString("t_tel"));
				manager.setT_password(rs.getString("t_password"));
				manager.setM_id(rs.getString("m_id"));
				manager.setIssuper(rs.getString("issuper"));
				arrayList.add(manager);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arrayList;
	}
	
	public static Manager select(String t_id) {
		Connection conn = DbConnect.getConnect();
		Statement stmt = null;
		ResultSet rs = null;
		Manager manager = new Manager();
		String sql;
		sql = "SELECT * FROM department where t_id = '" + t_id + "'";
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				manager.setT_id(rs.getString("t_id"));
				manager.setT_name(rs.getString("t_name"));
				manager.setT_tel(rs.getString("t_tel"));
				manager.setT_password(rs.getString("t_password"));
				manager.setM_id(rs.getString("m_id"));
				manager.setIssuper(rs.getString("issuper"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return manager;
	}
	
	public static int delete(String t_id) {
		Connection conn = DbConnect.getConnect();
		PreparedStatement stmt = null;
		int res = -1;
		String sql = "delete from teacher where t_id = ?";
		try {
			stmt = (PreparedStatement) conn.prepareStatement(sql);
			stmt.setString(1, t_id);
			res = stmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		try {
			conn.close();
			stmt.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return res;
	}
}
